# _*_ coding:utf-8 _*_
# @Time  : 2022.07.02
# @Author: zizlee
import datetime

import pandas as pd
from typing import Optional
from io import BytesIO
from fastapi import APIRouter, Header, Query, UploadFile, Form, Body
from pydantic import BaseModel, datetime_parse, validator, ValidationError
from db import FAConnection
from hutool import security
from logger import logger
from v1_all_api import constant
from v1_all_api.all_utils import datetime_utils, data_utils
from v1_all_api.all_response import AllResponse

from . import work_utils
short_message_api = APIRouter()


@short_message_api.get('/')  # 查询短讯通记录
def query_short_message(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                        page: int = Query(1, ge=1), page_size: int = Query(10, ge=5),
                        user: int = Query(0, ge=0)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    user_id = 'NOUSER' if not user else user
    query_sql = """
        SELECT a.id,a.create_date,a.user_id,a.content,a.msg_type,a.effects,a.note,a.audit_mind,a.editable,
            b.ruizy_code,b.admin_name   
        FROM work_short_message As a 
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.create_date>=%s AND a.create_date<=%s 
        AND IF('NOUSER'=%s,TRUE,a.user_id=%s) 
        ORDER BY create_date DESC,id DESC;
    """
    query_prm = [start_date, end_date, user_id, user]
    db_conn = FAConnection()
    ret = db_conn.query_paginator(query_sql, param=query_prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['audit_mind_zh'] = constant.SHORT_MESSAGE_AUDIT_TYPES.get(item['audit_mind'], '未知')
    return AllResponse.operate_successfully(data=ret)


@short_message_api.post('/my/fileUpdate/')  # 用户使用文件更新短讯通
async def update_user_message(ruizy_token: str = Header(...), file: UploadFile = Form(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    content = await file.read()
    try:
        df = pd.read_excel(BytesIO(content), sheet_name='短讯通记录', converters={0: data_utils.datetime_column_converter})
        df.dropna(subset=['日期'], inplace=True)
        if df.columns.tolist() != ['日期', '信息内容', '类别', '影响品种', '备注']:
            raise ValueError('文件列信息错误!')
        await file.close()
    except Exception as e:
        logger.error('读取短讯通文件错误!{}'.format(e))
        return AllResponse.validate_error(msg='解析短讯通信息失败,请检查文件格式！')
    df = df.rename(columns={'日期': 'create_date', '信息内容': 'content', '类别': 'msg_type', '影响品种': 'effects',
                            '备注': 'note'})
    # 去除日期为nan的行
    df.dropna(subset=['create_date'], inplace=True)
    # 填充其他nan为‘’
    df.fillna(value='', inplace=True)
    # 内容stripe一下
    df['content'] = df['content'].apply(lambda x: x.strip())
    # 选择内容不为空的行
    df = df[df['content'] != '']
    # 添加author列
    df['user_id'] = person['uid']
    # 处理create_date列
    df['create_date'] = df['create_date'].apply(lambda x: x.strftime('%Y%m%d'))
    # 查询系统中当前用户最大的短信通信息日期
    max_date_sql = "SELECT MAX(create_date) As max_date FROM work_short_message WHERE user_id=%s;"
    db_conn = FAConnection()
    max_date = db_conn.query(max_date_sql, param=[person['uid']], keep_conn=True, fetchone=True)[0]['max_date']
    if max_date:
        # 截取数据
        df = df[df['create_date'] > str(max_date)]
    if df.empty:
        db_conn.close()
        return AllResponse.operate_successfully(msg='没有检测到新的数据！')
    # 保存数据
    save_list = df.to_dict(orient='records')
    insert_sql = """
        INSERT INTO work_short_message(create_date,user_id,content,msg_type,effects,note) 
        VALUES (%(create_date)s,%(user_id)s,%(content)s,%(msg_type)s,%(effects)s,%(note)s);
    """
    count, success = db_conn.execute(insert_sql, param=save_list, many=True)
    msg = f'添加短讯通成功，新增数量{count}.'
    return AllResponse.operate_successfully(msg=msg) if success else AllResponse.server_error()


class MessageItem(BaseModel):
    create_date: datetime_parse.date
    content: str
    msg_type: str
    effects: str
    note: str


@short_message_api.post('/my/')  # 手动添加短讯通
def create_user_message(ruizy_token: str = Header(...), msg_item: MessageItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 添加
    sql = """
        INSERT INTO work_short_message (create_date,user_id,content,msg_type,effects,note) VALUES (%s,%s,%s,%s,%s,%s);
    """
    prm = (msg_item.create_date.strftime('%Y%m%d'), person['uid'], msg_item.content, msg_item.msg_type,
           msg_item.effects, msg_item.note)
    db_conn = FAConnection()
    _, success = db_conn.insert(sql, param=prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@short_message_api.get('/my/')  # 查询用户的短讯通
def query_user_message(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    sql = """
        SELECT id,create_date,content,msg_type,effects,note,audit_mind,editable FROM work_short_message 
        WHERE user_id=%s AND create_date>=%s AND create_date<=%s 
        ORDER BY create_date DESC,id DESC;
    """
    prm = (person['uid'], start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    # 统计一下当前月的数据
    today = datetime.datetime.today()
    month_start, month_end = datetime_utils.month_start_end(year=today.year, month=today.month, rf='%Y%m%d')
    current_month_count = len(list(filter(lambda x: month_start <= str(x['create_date']) <= month_end, records)))
    response = {
        'count': current_month_count,
        'data': records
    }
    return AllResponse.operate_successfully(data=response)


class DeleteParamItem(BaseModel):
    start: Optional[datetime_parse.date]
    end: Optional[datetime_parse.date]
    mid: Optional[int]


@short_message_api.delete('/my/')  # 用户删除短讯通
def delete_user_message(ruizy_token: str = Header(...), param: DeleteParamItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if param.start and param.end:
        sql = "DELETE FROM work_short_message WHERE user_id=%s AND create_date>=%s AND create_date<=%s;"
        prm = (person['uid'], param.start.strftime('%Y%m%d'), param.end.strftime('%Y%m%d'))
    else:
        if not param.mid:
            return AllResponse.validate_error(msg='参数错误!')
        # 删除数据
        sql = "DELETE FROM work_short_message WHERE id=%s AND user_id=%s;"
        prm = (param.mid, person['uid'])
    db_conn = FAConnection()
    count, success = db_conn.execute(sql, param=prm)
    rep_msg = '操作成功，删除了{}条数据!'.format(count)
    return AllResponse.operate_successfully(msg=rep_msg) if success else AllResponse.server_error()


@short_message_api.get('/statistics/')  # 按日期范围统计短讯通
def statistics_message(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                       st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    # 查询数据
    sql = """
        SELECT a.create_date,a.id,a.user_id,a.audit_mind,a.content,b.admin_name  
        FROM work_short_message As a 
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.create_date>=%s AND a.create_date<=%s
        ORDER BY a.create_date DESC;
    """
    prm = (start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))

    user_dict = {}
    total_audit_count = 0
    for msg in records:
        audit_count = 0
        if msg['audit_mind'] != 'A00':
            audit_count = 1
            total_audit_count += 1

        if msg['user_id'] in user_dict.keys():
            obj = user_dict[msg['user_id']]
            obj['total_count'] += 1
            obj['audit_count'] += audit_count
        else:
            user_dict[msg['user_id']] = {
                'user_id': msg['user_id'],
                'admin_name': msg['admin_name'],
                'total_count': 1,
                'audit_count': audit_count
            }
    statistics_data = [v for _, v in user_dict.items()]
    statistics_data.sort(key=lambda x: x['total_count'], reverse=True)
    if len(statistics_data) > 0:
        # 加入合计列
        statistics_data.append({
            'user_id': -1,
            'admin_name': '合计',
            'total_count': len(records),
            'audit_count': total_audit_count
        })
    response_data = {
        'statistics': statistics_data,
        'records': records
    }
    return AllResponse.operate_successfully(data=response_data)


@short_message_api.get('/statisticsMonthly/')  # 按月份统计数量
def statistics_investment_monthly(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                  st: int = Query(1, ge=1, le=2)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    # 查询数据
    sql = """
            SELECT a.create_date,a.id,a.user_id,a.audit_mind,a.content,b.admin_name  
            FROM work_short_message As a 
            INNER JOIN ruizy_user As b ON a.user_id=b.id 
            WHERE a.create_date>=%s AND a.create_date<=%s
            ORDER BY a.create_date DESC;
        """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=(start_date, end_date))
    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))

    month_list = datetime_utils.month_of_range(start=start_date, end=end_date, ft='%Y%m%d')
    # 对数据按人员按月份统计
    statistics_data, table_columns = work_utils.statistics_monthly(records, month_list=month_list)
    response_data = {
        'statistics_data': statistics_data,
        'table_columns': table_columns
    }
    return AllResponse.operate_successfully(data=response_data)


class PutMessageItem(BaseModel):
    sid: int
    audit_mind: str

    @validator('audit_mind')
    def validate_audit_mind(cls, value):
        if value not in constant.SHORT_MESSAGE_AUDIT_TYPES.keys():
            raise ValidationError('audit_mind has not supported!')
        return value


@short_message_api.put('/')  # 管理员修改短讯通数据
def modify_short_message(ruizy_token: str = Header(...), msg: PutMessageItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = "UPDATE work_short_message SET audit_mind=%s WHERE id=%s LIMIT 1;"
    db_conn = FAConnection()
    _, success = db_conn.execute(sql, param=[msg.audit_mind, msg.sid])
    resp = {
        'id': msg.sid,
        'audit_mind': msg.audit_mind,
        'audit_mind_zh': constant.SHORT_MESSAGE_AUDIT_TYPES.get(msg.audit_mind)
    }
    return AllResponse.operate_successfully(data=resp) if success else AllResponse.server_error()
